package dao.impl;

import dao.BookDao;
import pojo.Book;
import util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Tiler on 2020/11/4
 */
public class BookDaoImpl implements BookDao {
    @Override
    public Integer insertOneBook(Book book) {
        JDBCUtil jdbcUtil = new JDBCUtil();

        String sql = "insert into book(bookName, image, publisherId) values (?, ?, ?)";

        //插入记录并返回自增id
        Integer status = jdbcUtil.excuteUpdate(sql, true, book.getBookName(), book.getImage(), book.getPublisherId());

        jdbcUtil.close();

        return status;
    }

    @Override
    public Integer selectBookCount(Map<String, Object> params) {
        String sql = "select count(distinct b.bookId) from relation r left join book b on r.bookId = b.bookId ";

        sql += createCondition(params);

        System.out.println(sql);

        JDBCUtil jdbcUtil = new JDBCUtil();

        ResultSet resultSet = jdbcUtil.excuteQuery(sql);

        try {
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            else {
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();

            return null;
        }
        finally {
            jdbcUtil.close();
        }
    }

    //生成where条件子句
    private String createCondition(Map<String, Object> params) {
        StringBuffer buffer = new StringBuffer();

        String name = (String) params.get("name");
        if (name != null && !name.equals("")) {
            buffer.append("bookName like '%" + name + "%' ");
        }

        Integer pub = (Integer) params.get("pub");
        if (pub != null && pub != 0) {
            if (buffer.length() > 0) {
                buffer.append("and ");
            }

            buffer.append("publisherId = " + pub + " ");
        }

        String cate = (String) params.get("cate");
        if (cate != null && !cate.equals("")) {
            if (buffer.length() > 0) {
                buffer.append("and (");
            }
            else {
                buffer.append("(");
            }

            String[] cids = cate.split(",");

            for (String cid : cids) {
                //如果buffer最后一个字符不是(
                if (buffer.charAt(buffer.length()-1) != '(') {
                    buffer.append(" or ");
                }

                buffer.append("categoryId=" + cid);
            }

            buffer.append(") ");
        }

        if (buffer.length() > 0) {
            return "where " + buffer.toString();
        }
        else {
            return "";
        }
    }

    @Override
    public List<Book> selectAllBooks(Map<String, Object> params) {
        List<Book> books = new ArrayList<>();

        //String sql = "select b.*, p.publisherName from book b inner join publisher p on b.publisherId = p.publisherId ";
        String sql = "select distinct b.* from relation r left join book b on r.bookId = b.bookId ";

        Integer start = (Integer) params.get("start");
        Integer length = (Integer) params.get("length");

        //拼接where子句
        sql += createCondition(params);

        sql += "order by bookId limit ?, ?";

        System.out.println(sql);

        JDBCUtil jdbcUtil = new JDBCUtil();

        ResultSet resultSet = jdbcUtil.excuteQuery(sql, start, length);

        try {
            while(resultSet.next()) {
                Book book = new Book();

                book.setBookId(resultSet.getInt("bookId"));
                book.setBookName(resultSet.getString("bookName"));
                book.setImage(resultSet.getString("image"));
                book.setPublisherId(resultSet.getInt("publisherId"));
                //book.setPublisherName(resultSet.getString("publisherName"));

                books.add(book);
            }

            return books;
        } catch (SQLException e) {
            e.printStackTrace();

            return null;
        }
        finally {
            jdbcUtil.close();
        }
    }

    @Override
    public Book selectBookById(Integer bookId) {
        Book book = null;

        String sql = "select b.*, p.publisherName from book b inner join publisher p on b.publisherId = p.publisherId " +
                "where b.bookId = ?";

        JDBCUtil jdbcUtil = new JDBCUtil();

        ResultSet resultSet = jdbcUtil.excuteQuery(sql, bookId);

        try {
            if (resultSet.next()) {
                book = new Book();

                book.setBookId(resultSet.getInt("bookId"));
                book.setBookName(resultSet.getString("bookName"));
                book.setImage(resultSet.getString("image"));
                book.setPublisherId(resultSet.getInt("publisherId"));
                book.setPublisherName(resultSet.getString("publisherName"));
            }

            return book;
        } catch (SQLException e) {
            e.printStackTrace();

            return null;
        }
        finally {
            jdbcUtil.close();
        }
    }

    @Override
    public Integer updateOneBook(Book book) {
        JDBCUtil jdbcUtil = new JDBCUtil();

        String sql = "update book set bookName = ?, image = ?, publisherId = ? where bookId = ?";

        //插入记录并返回自增id
        Integer status = jdbcUtil.excuteUpdate(sql, false, book.getBookName(), book.getImage(), book.getPublisherId(), book.getBookId());

        jdbcUtil.close();

        return status;
    }

    @Override
    public Integer deleteAllBooks(List<Integer> bookIds) {
        JDBCUtil jdbcUtil = new JDBCUtil();

        String sql = "delete from book where bookId in ";

        StringBuffer buffer = new StringBuffer();

        for (Integer bookId : bookIds) {
            if (buffer.length() > 0) {
                buffer.append(",");
            }

            buffer.append(bookId);
        }

        sql += "(" + buffer.toString() + ")";

        System.out.println(sql);

        Integer status = jdbcUtil.excuteUpdate(sql, false);

        jdbcUtil.close();

        return status;
    }
}
